<!DOCTYPE html>
<html lang="zh-Hans">
<head>

    <!-- hexo-inject:begin --><!-- hexo-inject:end --><!--[if lt IE 9]>
        <style>body {display: none; background: none !important} </style>
        <meta http-equiv="Refresh" Content="0; url=//outdatedbrowser.com/" />
    <![endif]-->

<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge, chrome=1" />
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
<meta name="format-detection" content="telephone=no" />
<meta name="author" content="Dragonflyxyz" />



<meta name="description" content="作者的公司是一家网络游戏公司,其中网站交易与游戏数据库结合通过ws实现的,但是交易记录存放在网站上,级别是千万级别的数据库是mysql数据库.可能有人会问mysql是否支持千万级数据库,还有既然已经到了这个数据量公司肯定不差,为什么要用mysql而不用oracle这里我做一下解答">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql数据库千万级别数据的查询优化和分页测试">
<meta property="og:url" content="https://dragonflyxyz.github.io/2015/03/06/mysql-10millions-optimize-query/index.html">
<meta property="og:site_name" content="Dragonflyxyz的知识整理">
<meta property="og:description" content="作者的公司是一家网络游戏公司,其中网站交易与游戏数据库结合通过ws实现的,但是交易记录存放在网站上,级别是千万级别的数据库是mysql数据库.可能有人会问mysql是否支持千万级数据库,还有既然已经到了这个数据量公司肯定不差,为什么要用mysql而不用oracle这里我做一下解答">
<meta property="og:updated_time" content="2016-10-16T11:29:52.260Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Mysql数据库千万级别数据的查询优化和分页测试">
<meta name="twitter:description" content="作者的公司是一家网络游戏公司,其中网站交易与游戏数据库结合通过ws实现的,但是交易记录存放在网站上,级别是千万级别的数据库是mysql数据库.可能有人会问mysql是否支持千万级数据库,还有既然已经到了这个数据量公司肯定不差,为什么要用mysql而不用oracle这里我做一下解答">

<link rel="apple-touch-icon" href= "/apple-touch-icon.png">


    <link rel="alternate" href="/atom.xml" title="Dragonflyxyz的知识整理" type="application/atom+xml">



    <link rel="shortcut icon" href="/favicon.png">



    <link href="//cdn.bootcss.com/animate.css/3.5.1/animate.min.css" rel="stylesheet">



    <link href="//cdn.bootcss.com/fancybox/2.1.5/jquery.fancybox.min.css" rel="stylesheet">



    <script src="//cdn.bootcss.com/pace/1.0.2/pace.min.js"></script>
    <link href="//cdn.bootcss.com/pace/1.0.2/themes/blue/pace-theme-minimal.css" rel="stylesheet">


<link rel="stylesheet" href="/css/style.css">



<link href="//cdn.bootcss.com/font-awesome/4.6.3/css/font-awesome.min.css" rel="stylesheet">


<title>Mysql数据库千万级别数据的查询优化和分页测试 | Dragonflyxyz的知识整理</title>

<script src="//cdn.bootcss.com/jquery/2.2.4/jquery.min.js"></script>
<script src="//cdn.bootcss.com/clipboard.js/1.5.10/clipboard.min.js"></script>

<script>
    var yiliaConfig = {
        fancybox: true,
        animate: true,
        isHome: false,
        isPost: true,
        isArchive: false,
        isTag: false,
        isCategory: false,
        fancybox_js: "//cdn.bootcss.com/fancybox/2.1.5/jquery.fancybox.min.js",
        scrollreveal: "//cdn.bootcss.com/scrollReveal.js/3.1.4/scrollreveal.min.js",
        search: true
    }
</script>


    <script> yiliaConfig.jquery_ui = [false]; </script>



    <script> yiliaConfig.rootUrl = "\/";</script><!-- hexo-inject:begin --><!-- hexo-inject:end -->






</head>
<body>
  <!-- hexo-inject:begin --><!-- hexo-inject:end --><div id="container">
    <div class="left-col">
    <div class="overlay"></div>
<div class="intrude-less">
    <header id="header" class="inner">
        <a href="/" class="profilepic">
            <img src="/img/avatar.png" class="animated zoomIn">
        </a>
        <hgroup>
          <h1 class="header-author"><a href="/">Dragonflyxyz</a></h1>
        </hgroup>

        

        
            <form id="search-form">
            <input type="text" id="local-search-input" name="q" placeholder="search..." class="search form-control" autocomplete="off" autocorrect="off" searchonload="false" />
            <i class="fa fa-times" onclick="resetSearch()"></i>
            </form>
            <div id="local-search-result"></div>
            <p class='no-result'>No results found <i class='fa fa-spinner fa-pulse'></i></p>
        


        
            <div id="switch-btn" class="switch-btn">
                <div class="icon">
                    <div class="icon-ctn">
                        <div class="icon-wrap icon-house" data-idx="0">
                            <div class="birdhouse"></div>
                            <div class="birdhouse_holes"></div>
                        </div>
                        <div class="icon-wrap icon-ribbon hide" data-idx="1">
                            <div class="ribbon"></div>
                        </div>
                        
                        <div class="icon-wrap icon-link hide" data-idx="2">
                            <div class="loopback_l"></div>
                            <div class="loopback_r"></div>
                        </div>
                        
                        
                        <div class="icon-wrap icon-me hide" data-idx="3">
                            <div class="user"></div>
                            <div class="shoulder"></div>
                        </div>
                        
                    </div>
                    
                </div>
                <div class="tips-box hide">
                    <div class="tips-arrow"></div>
                    <ul class="tips-inner">
                        <li>菜单</li>
                        <li>标签</li>
                        
                        <li>友情链接</li>
                        
                        
                        <li>关于我</li>
                        
                    </ul>
                </div>
            </div>
        

        <div id="switch-area" class="switch-area">
            <div class="switch-wrap">
                <section class="switch-part switch-part1">
                    <nav class="header-menu">
                        <ul>
                        
                            <li><a href="/">主页</a></li>
                        
                            <li><a href="/archives/">所有文章</a></li>
                        
                            <li><a href="/tags/">标签云</a></li>
                        
                            <li><a href="/about/">关于我</a></li>
                        
                        </ul>
                    </nav>
                    <nav class="header-nav">
                        <ul class="social">
                            
                                <a class="fa Email" href="mailto:123@123.com" title="Email"></a>
                            
                                <a class="fa GitHub" href="#" title="GitHub"></a>
                            
                                <a class="fa RSS" href="/atom.xml" title="RSS"></a>
                            
                        </ul>
                    </nav>
                </section>
                
                
                <section class="switch-part switch-part2">
                    <div class="widget tagcloud" id="js-tagcloud">
                        <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/tags/AES/">AES</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/AI/">AI</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Bigdata/">Bigdata</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/C/">C</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/C/">C#</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/C/">C++</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/DeepLearning/">DeepLearning</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Hadoop/">Hadoop</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/IOT/">IOT</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Java/">Java</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Linux/">Linux</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/MIT/">MIT</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/MachineLearning/">MachineLearning</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Mathematics/">Mathematics</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Matlab/">Matlab</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Microsoft/">Microsoft</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Node-js/">Node.js</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/OpenCV/">OpenCV</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Python/">Python</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/R/">R</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/RDMS/">RDMS</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/RTOS/">RTOS</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Ruby/">Ruby</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/SketchUp/">SketchUp</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/Vision/">Vision</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/arduino/">arduino</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/bigdata/">bigdata</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/blog/">blog</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/chip/">chip</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/code/">code</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/hexo/">hexo</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/homedesign/">homedesign</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/intel/">intel</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/mysql/">mysql</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/opensource/">opensource</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/optimize/">optimize</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/others/">others</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/php/">php</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/picture/">picture</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/python/">python</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/robot/">robot</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/science/">science</a></li><li class="tag-list-item"><a class="tag-list-link" href="/tags/solution/">solution</a></li></ul>
                    </div>
                </section>
                
                
                
                <section class="switch-part switch-part3">
                    <div id="js-friends">
                    
                      <a class="main-nav-link switch-friends-link" href="https://hexo.io">Hexo</a>
                    
                      <a class="main-nav-link switch-friends-link" href="https://pages.github.com/">GitHub</a>
                    
                      <a class="main-nav-link switch-friends-link" href="http://moxfive.xyz/">MOxFIVE</a>
                    
                    </div>
                </section>
                

                
                
                <section class="switch-part switch-part4">
                
                    <div id="js-aboutme">专注于前端</div>
                </section>
                
            </div>
        </div>
    </header>                
</div>
    </div>
    <div class="mid-col">
      <nav id="mobile-nav">
      <div class="overlay">
          <div class="slider-trigger"></div>
          <h1 class="header-author js-mobile-header hide"><a href="/" title="回到主页">Dragonflyxyz</a></h1>
      </div>
    <div class="intrude-less">
        <header id="header" class="inner">
            <a href="/" class="profilepic">
                <img src="/img/avatar.png" class="animated zoomIn">
            </a>
            <hgroup>
              <h1 class="header-author"><a href="/" title="回到主页">Dragonflyxyz</a></h1>
            </hgroup>
            
            <nav class="header-menu">
                <ul>
                
                    <li><a href="/">主页</a></li>
                
                    <li><a href="/archives/">所有文章</a></li>
                
                    <li><a href="/tags/">标签云</a></li>
                
                    <li><a href="/about/">关于我</a></li>
                
                <div class="clearfix"></div>
                </ul>
            </nav>
            <nav class="header-nav">
                        <ul class="social">
                            
                                <a class="fa Email" target="_blank" href="mailto:123@123.com" title="Email"></a>
                            
                                <a class="fa GitHub" target="_blank" href="#" title="GitHub"></a>
                            
                                <a class="fa RSS" target="_blank" href="/atom.xml" title="RSS"></a>
                            
                        </ul>
            </nav>
        </header>                
    </div>
    <link class="menu-list" tags="标签" friends="友情链接" about="关于我"/>
</nav>
      <div class="body-wrap"><article id="post-mysql-10millions-optimize-query" class="article article-type-post" itemscope itemprop="blogPost">
  
    <div class="article-meta">
      <a href="/2015/03/06/mysql-10millions-optimize-query/" class="article-date">
      <time datetime="2015-03-06T11:23:36.000Z" itemprop="datePublished">2015-03-06</time>
</a>


    </div>
  
  <div class="article-inner">
    
      <input type="hidden" class="isFancy" />
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      Mysql数据库千万级别数据的查询优化和分页测试
    </h1>
  

      </header>
      
      <div class="article-info article-info-post">
        
    <div class="article-category tagcloud">
    <a class="article-category-link" href="/categories/database/">database</a>
    </div>


        
    <div class="article-tag tagcloud">
        <ul class="article-tag-list"><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/mysql/">mysql</a></li><li class="article-tag-list-item"><a class="article-tag-list-link" href="/tags/optimize/">optimize</a></li></ul>
    </div>

        <div class="clearfix"></div>
      </div>
      
    
    <div class="article-entry" itemprop="articleBody">
      
          
        <p>作者的公司是一家网络游戏公司,其中网站交易与游戏数据库结合通过ws实现的,但是交易记录存放在网站上,级别是千万级别的数据库是mysql数据库.<br>可能有人会问mysql是否支持千万级数据库,还有既然已经到了这个数据量公司肯定不差,为什么要用mysql而不用oracle这里我做一下解答<br><a id="more"></a></p>
<ol>
<li>mysql绝对支持千万级数据库是可以肯定的，</li>
<li>为什么选择择mysql呢？<br>1&gt; 第一也是最主要的一条是mysql他能做到。<br>2&gt; 在第一点前提下以下的就不是太重要了,mysql相对操作简单,测试容易,配置优化也相对容易很多<br>3&gt; 我们这里的数据仅仅是为了记录交易保证交易是被记录的,对于查询的还是相对少只有管理后台操作中需要对数据库进行查询<br>4&gt; 数据结构简单,而且每条记录都非常小,因为查询速度不管和记录条数有关和数据文件大小也有直接关系.<br>5&gt; 我们采用的是大小表的解决办法,每天大概需要插入数据库好几百万条,这里可能还是有人怀疑,其实没问题,如果批量插入我测试的在普通的pc机子上带该一个 线程并发我插入的是6千万条记录大概需要“JDBC插入6000W条数据用时:9999297ms”,小表保存最近插入的内容,把几天前的保存到大表中, 这里我说的就是大表大概6-7千万条数据;<br>带着这些疑问和求知欲望咱们来做一个测试，因为在那个时候我也不是dba不知道人家是怎么搞的能够做成这么大的数据量,我们平时叶总探讨一些相关的内容<br>1.mysql的数据查询,大小字段要分开,这个还是有必要的,除非一点就是你查询的都是索引内容而不是表内容,比如只查询id等等<br>2.查询速度和索引有很大关系也就是索引的大小直接影响你的查询效果,但是查询条件一定要建立索引,这点上注意的是索引字段不能太多，太多索引文件就会很大那样搜索只能变慢,<br>3.查询指定的记录最好通过Id进行in查询来获得真实的数据.其实不是最好而是必须，也就是你应该先查询出复合的ID列表,通过in查询来获得数据<br>我们来做一个测试ipdatas表：<br>CREATE TABLE <code>ipdatas</code> (<br><code>id</code> INT(11) NOT NULL AUTO_INCREMENT,<br><code>uid</code> INT(8) NOT NULL DEFAULT ‘0’,<br><code>ipaddress</code> VARCHAR(50) NOT NULL,<br><code>source</code> VARCHAR(255) DEFAULT NULL,<br><code>track</code> VARCHAR(255) DEFAULT NULL,<br><code>entrance</code> VARCHAR(255) DEFAULT NULL,<br><code>createdtime</code> DATETIME NOT NULL DEFAULT ‘0000-00-00 00:00:00’,<br><code>createddate</code> DATE NOT NULL DEFAULT ‘0000-00-00’,<br>PRIMARY KEY (<code>id</code>),<br>KEY <code>uid</code> (<code>uid</code>)<br>) ENGINE=MYISAM AUTO_INCREMENT=67086110 DEFAULT CHARSET=utf8;<br>这是我们做的广告联盟的推广ip数据记录表，由于我也不是mysql的DBA所以这里咱们仅仅是测试<br>因为原来里面有大概7015291条数据<br>这里我们通过jdbc的batch插入6000万条数据到此表当中“JDBC插入6000W条数据用时:9999297ms”；<br>大概用了两个多小时,这里面我用的是batch大小大概在1w多每次提交,还有一点是每次提交的数据都很小，而且这里用的myisam数据表，因为我需要知道mysql数据库的大小以及索引数据的大小结果是<br>ipdatas.MYD 3.99 GB (4,288,979,008 字节)<br>ipdatas.MYI 1.28 GB (1,377,600,512 字节)<br>这里面我要说的是如果真的是大数据如果时间需要索引还是最好改成数字字段,索引的大小和查询速度都比时间字段可观。<br>步入正题:<br>1.全表搜索<br>返回结构是67015297条数据<br>SELECT COUNT(id) FROM ipdatas;<br>SELECT COUNT(uid) FROM ipdatas;<br>SELECT COUNT(<em>) FROM ipdatas;<br>首先这两个全表数据查询速度很快,mysql中包含数据字典应该保留了数据库中的最大条数<br>查询索引条件<br>SELECT COUNT(</em>) FROM ipdatas WHERE uid=1;   返回结果时间:2分31秒594<br>SELECT COUNT(id) FROM ipdatas WHERE uid=1;  返回结果时间:1分29秒609<br>SELECT COUNT(uid) FROM ipdatas WHERE uid=1; 返回结果时间:2分41秒813<br>第二次查询都比较快因为mysql中是有缓存区的所以增大缓存区的大小可以解决很多查询的优化，真可谓缓存无处不在啊在程序开发中也是层层都是缓存<br>查询数据<br>第一条开始查询<br>SELECT <em> FROM ipdatas ORDER BY id DESC LIMIT 1,10 ; 31毫秒<br>SELECT </em> FROM ipdatas LIMIT 1,10 ; 15ms<br>第10000条开始查询<br>SELECT <em> FROM ipdatas ORDER BY id ASC LIMIT 10000,10 ; 266毫秒<br>SELECT </em> FROM ipdatas LIMIT 10000,10 ; 16毫秒<br>第500万条开始查询<br>SELECT <em> FROM ipdatas LIMIT 5000000,10 ;11.312秒<br>SELECT </em> FROM ipdatas ORDER BY id ASC LIMIT 5000000,10 ; 221.985秒<br>这两条返回结果完全一样,也就是mysql默认机制就是id正序然而时间却大相径庭<br>第5000万条开始查询<br>SELECT <em> FROM ipdatas LIMIT 60000000,10 ;66.563秒 (对比下面的测试)<br>SELECT </em> FROM ipdatas ORDER BY id ASC LIMIT 50000000,10; 1060.000秒<br>SELECT <em> FROM ipdatas ORDER BY id DESC LIMIT 17015307,10; 434.937秒<br>第三条和第二条结果一样只是排序的方式不同但是用时却相差不少，看来这点还是不如很多的商业数据库,像oracle和sqlserver等都是中间不成两边还是没问题，看来mysql是开始行越向后越慢，这里看来可以不排序的就不要排序了性能差距巨大,相差了20多倍<br>查询数据返回ID列表<br>第一条开始查<br>select id from ipdatas order by id asc limit 1,10; 31ms<br>SELECT id FROM ipdatas LIMIT 1,10 ; 0ms<br>第10000条开始<br>SELECT id FROM ipdatas ORDER BY id ASC LIMIT 10000,10; 68ms<br>select id from ipdatas limit 10000,10;0ms<br>第500万条开始查询<br>SELECT id FROM ipdatas LIMIT 5000000,10; 1.750s<br>SELECT id FROM ipdatas ORDER BY id ASC LIMIT 5000000,10;14.328s<br>第6000万条记录开始查询<br>SELECT id FROM ipdatas LIMIT 60000000,10; 116.406s<br>SELECT id FROM ipdatas ORDER BY id ASC LIMIT 60000000,10; 136.391s<br>select id from ipdatas limit 10000002,10; 29.032s<br>select id from ipdatas limit 20000002,10; 24.594s<br>select id from ipdatas limit 30000002,10; 24.812s<br>select id from ipdatas limit 40000002,10; 28.750s  84.719s<br>select id from ipdatas limit 50000002,10; 30.797s  108.042s<br>select id from ipdatas limit 60000002,10; 133.012s  122.328s<br>select </em> from ipdatas limit 10000002,10; 27.328s<br>select <em> from ipdatas limit 20000002,10; 15.188s<br>select </em> from ipdatas limit 30000002,10; 45.218s<br>select <em> from ipdatas limit 40000002,10; 49.250s   50.531s<br>select </em> from ipdatas limit 50000002,10; 73.297s   56.781s<br>select <em> from ipdatas limit 60000002,10; 67.891s   75.141s<br>select id from ipdatas order by id asc limit 10000002,10; 29.438s<br>select id from ipdatas order by id asc limit 20000002,10; 24.719s<br>select id from ipdatas order by id asc limit 30000002,10; 25.969s<br>select id from ipdatas order by id asc limit 40000002,10; 29.860d<br>select id from ipdatas order by id asc limit 50000002,10; 32.844s<br>select id from ipdatas order by id asc limit 60000002,10; 34.047s<br>至于SELECT </em> ipdatas order by id asc 就不测试了 大概都在十几分钟左右<br>可见通过SELECT id 不带排序的情况下差距不太大,加了排序差距巨大<br>下面看看这条语句<br>SELECT * FROM ipdatas WHERE id IN (10000,100000,500000,1000000,5000000,10000000,2000000,30000000,40000000,50000000,60000000,67015297);<br>耗时0.094ms<br>可见in在id上面的查询可以忽略不计毕竟是6000多万条记录，所以为什么很多lucene或solr搜索都返回id进行数据库重新获得数据就是因为这 个,当然lucene/solr+mysql是一个不错的解决办法这个非常适合前端搜索技术,比如前端的分页搜索通过这个可以得到非常好的性能.还可以支 持很好的分组搜索结果集,然后通过id获得数据记录的真实数据来显示效果真的不错,别说是千万级别就是上亿也没有问题,真是吐血推荐啊.<br>上面的内容还没有进行有条件的查询仅仅是一些关于orderby和limit的测试,请关注我的下一篇文件对于条件查询的1亿数据检索测试</li>
</ol>
<p>原文：<a href="http://blog.sina.com.cn/s/blog_438308750100im0b.html" target="_blank" rel="external">http://blog.sina.com.cn/s/blog_438308750100im0b.html</a></p>

      
    </div>
    
  </div>
  
    
    <div class="copyright">
        <p><span>本文标题:</span><a href="/2015/03/06/mysql-10millions-optimize-query/">Mysql数据库千万级别数据的查询优化和分页测试</a></p>
        <p><span>文章作者:</span><a href="/" title="回到主页">Dragonflyxyz</a></p>
        <p><span>发布时间:</span>2015-03-06, 19:23:36</p>
        <p><span>最后更新:</span>2016-10-16, 19:29:52</p>
        <p>
            <span>原始链接:</span><a class="post-url" href="/2015/03/06/mysql-10millions-optimize-query/" title="Mysql数据库千万级别数据的查询优化和分页测试">https://dragonflyxyz.github.io/2015/03/06/mysql-10millions-optimize-query/</a>
            <span class="copy-path" data-clipboard-text="原文: https://dragonflyxyz.github.io/2015/03/06/mysql-10millions-optimize-query/　　作者: Dragonflyxyz" title="点击复制文章链接"><i class="fa fa-clipboard"></i></span>
            <script> var clipboard = new Clipboard('.copy-path'); </script>
        </p>
        <p>
            <span>许可协议:</span><i class="fa fa-creative-commons"></i> <a rel="license" href="http://creativecommons.org/licenses/by-nc-sa/4.0/" title="CC BY-NC-SA 4.0 International" target = "_blank">"署名-非商用-相同方式共享 4.0"</a> 转载请保留原文链接及作者。
        </p>
    </div>



    <nav id="article-nav">
        
            <div id="article-nav-newer" class="article-nav-title">
                <a href="/2015/10/16/homedesign-north-america/">
                    越来越喜欢北美的家庭装饰风格
                </a>
            </div>
        
        
            <div id="article-nav-older" class="article-nav-title">
                <a href="/2015/02/16/my-blog-set/">
                    博客设置
                </a>
            </div>
        
    </nav>

  
</article>

    <div id="toc" class="toc-article">
        <strong class="toc-title">文章目录</strong>
        
            
        
    </div>
    <style>
        .left-col .switch-btn,
        .left-col .switch-area {
            display: none;
        }
        .toc-level-3 i,
        .toc-level-3 ol {
            display: none !important;
        }
    </style>

    <input type="button" id="tocButton" value="隐藏目录"  title="点击按钮隐藏或者显示文章目录">

    <script>
        yiliaConfig.toc = ["隐藏目录", "显示目录", !!"false"];
    </script>



    
<div class="share">
    
        <div class="bdsharebuttonbox">
            <a href="#" class="fa fa-twitter bds_twi" data-cmd="twi" title="分享到推特"></a>
            <a href="#" class="fa fa-weibo bds_tsina" data-cmd="tsina" title="分享到新浪微博"></a>
            <a href="#" class="fa fa-qq bds_sqq" data-cmd="sqq" title="分享给 QQ 好友"></a>
            <a href="#" class="fa fa-files-o bds_copy" data-cmd="copy" title="复制网址"></a>
            <a href="#" class="fa fa fa-envelope-o bds_mail" data-cmd="mail" title="通过邮件分享"></a>
            <a href="#" class="fa fa-weixin bds_weixin" data-cmd="weixin" title="生成文章二维码"></a>
            <a href="#" class="fa fa-share-alt bds_more" data-cmd="more"></i></a>
        </div>
        <script>
            window._bd_share_config={
                "common":{"bdSnsKey":{},"bdText":"Mysql数据库千万级别数据的查询优化和分页测试　| Dragonflyxyz的知识整理　","bdMini":"2","bdMiniList":false,"bdPic":"","bdStyle":"0","bdSize":"24"},"share":{}};with(document)0[(getElementsByTagName('head')[0]||body).appendChild(createElement('script')).src='http://bdimg.share.baidu.com/static/api/js/share.js?v=89860593.js?cdnversion='+~(-new Date()/36e5)];
        </script>
    

    
</div>







    




    <div class="scroll" id="post-nav-button">
        
            <a href="/2015/10/16/homedesign-north-america/" title="上一篇: 越来越喜欢北美的家庭装饰风格">
                <i class="fa fa-angle-left"></i>
            </a>
        

        <a title="文章列表"><i class="fa fa-bars"></i><i class="fa fa-times"></i></a>

        
            <a href="/2015/02/16/my-blog-set/" title="下一篇: 博客设置">
                <i class="fa fa-angle-right"></i>
            </a>
        
    </div>

    <ul class="post-list"><li class="post-list-item"><a class="post-list-link" href="/2018/02/03/book-c-sharp-deconstructed/">Book《C# Deconstructed-Discover How C# Works on the .Net Framework》</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/03/26/mathematics-rookie-and-machine-learning/">数学菜鸟如何学习机器学习</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/03/23/automatic-sketchup-cn-3/">《Automatic SketchUp Creating 3-D Models in Ruby》中文翻译 11-13章</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/03/10/automatic-sketchup-cn-2/">《Automatic SketchUp Creating 3-D Models in Ruby》中文翻译7-10章</a></li><li class="post-list-item"><a class="post-list-link" href="/2017/03/09/automatic-sketchup-cn-1/">《Automatic SketchUp Creating 3-D Models in Ruby》中文翻译1-5章</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/12/29/python-opencv-learn-note01/">Python OpenCV学习记录</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/11/15/learn-deep-learning-zero-based-1/">零基础入门深度学习(1)-感知器</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/11/15/hexo-show-mathjax-and-table/">Hexo如何显示公式和表格</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/10/23/hexo-local-search-only-titile/">让Hexo的本地搜索只搜索标题</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/10/17/why-im-switching-from-python-to-nodejs/">为什么我从Python转战到Node.js</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/10/16/php-develop-1200examples/">《PHP开发实战1200例》</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/10/16/introduction-john-carmack/">第一人称射击游戏教父，首款3D游戏的开发者，传奇程序员——约翰·卡马克</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/10/16/Ebook-search-engine/">几个好的电子书搜索引擎</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/10/16/MIT-tech-review-2016/">麻省理工科技评论2016年1-6月</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/10/16/head-first-python/">《Head First Python》中文版</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/07/09/microsoft-opensource-malmo-ai/">微软开源Malmo AI项目平台 利用Minecraft构建复杂机器人任务</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/06/22/why-this-robot-in-the-grocery-store/">零售业有福了！大数据机器人卖场值班，点货超轻松</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/06/21/poor-zorro-linux-book/">穷佐罗的Linux书，知识点整理的很好</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/05/20/github-awesome-hadoop/">Hadoop学习资源集合（Github）</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/05/17/python-decorated-concurrency/">Python多线程(Decorated concurrency)</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/05/15/github-awesome-machine-learning/">Github上最全的机器学习资料集合</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/05/12/the-9-best-languages-form-crunching-data/">数据处理的 9 大编程语言</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/05/05/github-awesome-bigdata/">最全的大数据学习资料集合（Github）</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/05/01/a-low-scientist-how-to-get-nobel-prize/">一个逗逼的科研狗是如何成为诺贝尔奖得主的</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/04/23/how-much-a-chip/">一枚芯片的实际成本是多少？</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/04/22/awesome-python-site/">Awesome-python一个收集Python资源的网站</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/04/21/intel-releases-the-arduino-101-firmware-source-code/">Intel将两款arduino设备的实时操作系统开源</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/04/20/studying-the-language-and-structure-in-non-programmers-solutions/">《编程问题的非程序员解决方案之研究》</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/03/26/if-human-and-animals-change/">如果人和动物角色互换？这样的世界怎样想象</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/03/11/checksum-8bits-and-16bits/">checksum-8位和16位校验和代码示例</a></li><li class="post-list-item"><a class="post-list-link" href="/2016/02/06/easy-popular-science-doc-deep-learning/">关于深度学习可能最容易读进去的科普贴</a></li><li class="post-list-item"><a class="post-list-link" href="/2015/12/16/what-is-iot-ppt/">物联网是什么-ppt图</a></li><li class="post-list-item"><a class="post-list-link" href="/2015/12/14/100-BBC-recordfile-parents-with-children/">家长应该陪孩子一起看的100部BBC纪录片</a></li><li class="post-list-item"><a class="post-list-link" href="/2015/11/30/america-people-live-style/">美国日常生活习惯一瞥</a></li><li class="post-list-item"><a class="post-list-link" href="/2015/11/29/photo-wall-style/">教你轻松搞定相片墙壁搭配</a></li><li class="post-list-item"><a class="post-list-link" href="/2015/10/16/algorithm-detail-DES-AES-SHA/">DES、AES、SHA加密算法详解</a></li><li class="post-list-item"><a class="post-list-link" href="/2015/10/16/homedesign-north-america/">越来越喜欢北美的家庭装饰风格</a></li><li class="post-list-item"><a class="post-list-link" href="/2015/03/06/mysql-10millions-optimize-query/">Mysql数据库千万级别数据的查询优化和分页测试</a></li><li class="post-list-item"><a class="post-list-link" href="/2015/02/16/my-blog-set/">博客设置</a></li><li class="post-list-item"><a class="post-list-link" href="/2014/10/01/john-carmack-on-inlined-code/">John Carmack on inlined code</a></li><li class="post-list-item"><a class="post-list-link" href="/2014/05/01/hello-world/">Hello World</a></li><li class="post-list-item"><a class="post-list-link" href="/2014/01/16/test1/">test1</a></li></ul>




    <script>
        
    </script>
</div>
      <footer id="footer">
    <div class="outer">
        <div id="footer-info">
            <div class="footer-left">
                <i class="fa fa-copyright"></i> 
                2016-2018 Dragonflyxyz
            </div>
            <div class="footer-right">
                <a href="http://hexo.io/" target="_blank" title="快速、简洁且高效的博客框架">Hexo</a>  Theme <a href="https://github.com/MOxFIVE/hexo-theme-yelee" target="_blank" title="简而不减 Hexo 双栏博客主题  v3.5">Yelee</a> by MOxFIVE <i class="fa fa-heart animated infinite pulse"></i>
            </div>
        </div>
        
            <div class="visit">
                
                    <span id="busuanzi_container_site_pv" style='display:none'>
                        <span id="site-visit" title="本站到访数"><i class="fa fa-user" aria-hidden="true"></i><span id="busuanzi_value_site_uv"></span>
                        </span>
                    </span>
                
                
                    <span>| </span>
                
                
                    <span id="busuanzi_container_page_pv" style='display:none'>
                        <span id="page-visit"  title="本页阅读量"><i class="fa fa-eye animated infinite pulse" aria-hidden="true"></i><span id="busuanzi_value_page_pv"></span>
                        </span>
                    </span>
                
            </div>
        
    </div>
</footer>
    </div>
    
<script data-main="/js/main.js" src="//cdn.bootcss.com/require.js/2.2.0/require.min.js"></script>

    <script>
        $(document).ready(function() {
            var iPad = window.navigator.userAgent.indexOf('iPad');
            if (iPad > -1 || $(".left-col").css("display") === "none") {
                var bgColorList = ["#9db3f4", "#414141", "#e5a859", "#f5dfc6", "#c084a0", "#847e72", "#cd8390", "#996731"];
                var bgColor = Math.ceil(Math.random() * (bgColorList.length - 1));
                $("body").css({"background-color": bgColorList[bgColor], "background-size": "cover"});
            }
            else {
                var backgroundnum = 5;
                var backgroundimg = "url(/background/bg-x.jpg)".replace(/x/gi, Math.ceil(Math.random() * backgroundnum));
                $("body").css({"background": backgroundimg, "background-attachment": "fixed", "background-size": "cover"});
            }
        })
    </script>





<div class="scroll" id="scroll">
    <a href="#" title="返回顶部"><i class="fa fa-arrow-up"></i></a>
    <a href="#comments" onclick="load$hide();" title="查看评论"><i class="fa fa-comments-o"></i></a>
    <a href="#footer" title="转到底部"><i class="fa fa-arrow-down"></i></a>
</div>
<script>
    // Open in New Window
    
        var oOpenInNew = {
            
            
            
            
            
            
             archives: ".archive-article-title", 
             miniArchives: "a.post-list-link", 
            
             friends: "#js-friends a", 
             socail: ".social a" 
        }
        for (var x in oOpenInNew) {
            $(oOpenInNew[x]).attr("target", "_blank");
        }
    
</script>

<script async src="https://dn-lbstatics.qbox.me/busuanzi/2.3/busuanzi.pure.mini.js">
</script>
  </div><!-- hexo-inject:begin --><!-- Begin: Injected MathJax -->
<script type="text/x-mathjax-config">
  MathJax.Hub.Config({"tex2jax":{"inlineMath":[["$","$"],["\\(","\\)"]],"skipTags":["script","noscript","style","textarea","pre","code"],"processEscapes":true},"TeX":{"equationNumbers":{"autoNumber":"AMS"}}});
</script>

<script type="text/x-mathjax-config">
  MathJax.Hub.Queue(function() {
    var all = MathJax.Hub.getAllJax(), i;
    for(i=0; i < all.length; i += 1) {
      all[i].SourceElement().parentNode.className += ' has-jax';
    }
  });
</script>

<script type="text/javascript" src="//cdn.mathjax.org/mathjax/latest/MathJax.js?config=TeX-AMS-MML_HTMLorMML">
</script>
<!-- End: Injected MathJax -->
<!-- hexo-inject:end -->
</body>
</html>